﻿using System;
using System.Data;
using System.Collections.Generic;
using Chenxin.Lib.Hr.Entity;
using Chenxin.Lib.Utility;
using Chenxin.Lib.Business;
using System.Text;

namespace Chenxin.Lib.Business.BLL
{
    /// <summary>
    /// 业务逻辑类ContractApplyInfo 的摘要说明。
    /// </summary>
    public class ConfirmMainBusinessApply
    {
        public ConfirmMainBusinessApply()
        { }

        /// <summary>
        /// 业绩申请添加
        /// </summary>
        public void Add(Chenxin.Lib.Business.Entity.ContractApplyInfo model)
        {
            Chenxin.Lib.Business.DAL.ContractApplyInfo.Add(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(Chenxin.Lib.Business.Entity.ContractApplyInfo model)
        {
            Chenxin.Lib.Business.DAL.ContractApplyInfo.Update(model);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Chenxin.Lib.Business.Entity.ContractApplyInfo GetModel(Guid RecordId)
        {

            return Chenxin.Lib.Business.DAL.ContractApplyInfo.GetModel(RecordId);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Chenxin.Lib.Business.Entity.t_Contract_BaseInfo GetBaseModel(Guid RecordId)
        {

            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetModel(RecordId);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Chenxin.Lib.Business.Entity.t_Contract_PointAllotInfo> GetContractListArray(Guid id)
        {
            return Chenxin.Lib.Business.DAL.t_Contract_PointAllotInfo.GetListArray(string.Format(" ContractId = '{0}'", id));
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet getList(string strWhere, int strPageIndex, int strPageSize)
        {
            //排序
            string strOrderField = "a.AddTime desc";

            //欲选择字段列表 
            StringBuilder FieldList = new StringBuilder();
            FieldList.Append("ContractUserName,ContractId,ApplyId,ContractCode,ContractDate,BusinessDescribe,BusinessType,Status,");
            FieldList.Append("BargainType,BargainPrice,HouseId,");
            FieldList.Append("HouseAddress,Area,CommunityId,Community,PropertyCode,Region,Block,CurrentFloor,");
            FieldList.Append("TotalFloor,BuildingType,HouseUse,HouseType,BargainCompanyName,VenderDepartId,VenderDepartName,");
            FieldList.Append("VenderUserId,VenderUserName,VenderId,VenderName,VenderNo,VenderPhone,VenderMobile,");
            FieldList.Append("BuyerDepartId,BuyerDepartName,BuyerUserId,BuyerUserName,BuyerId,BuyerName,BuyerNo,BuyerPhone,BuyerMobile,");
            FieldList.Append("VStanCommision,BStanCommision,AppEmployee,AppDepart,ConfirmEmplyee,ConfirmDepart,AddTime,");
            FieldList.Append("VRealCommision,BRealCommision,VCommision,BCommision,VConsulting,BConsulting,VSTDiscount,BSTDiscount,STMemo,");
            FieldList.Append("UpdateTime,bArchivement,LockStatus,LastCreateArchDate,CreateArchEmp,PointRemark,ArchivementDate,CustomerSource,");
            FieldList.Append("VRealCommisionFinal,BRealCommisionFinal,VCommisionFinal,BCommisionFinal,VConsultingFinal,BConsultingFinal,");
            FieldList.Append("V_Departs,V_Users,V_Points,B_Departs,B_Users, B_Points,Departs,Users,Points,LackDocument,");
            FieldList.Append("VCommisionGeted,BCommisionGeted,VConsultingPayed,BConsultingPayed,BCommisionGetedPayed,VCommisionGetedPayed,");
            FieldList.Append("ContractCityId,ContractCityName,ContractCompanyId,ContractCompanyName,ContractDepartmentName,Memo,VNotGeted,BNotGeted,FixDiscountFinal,DiscountRate");
            
          
            //子查询欲选择字段列表
            StringBuilder sb = new StringBuilder();
            sb.Append("ContractUserName,a.ContractId,ApplyId,ContractCode,ContractDate,BusinessDescribe,BusinessType,a.Status,");
            sb.Append("case BargainType when 10 then '面签' else '授签' end as BargainType,BargainPrice,HouseId,");
            sb.Append("HouseAddress,Area,CommunityId,Community,PropertyCode,Region,Block,CurrentFloor,LackDocument,");
            sb.Append("TotalFloor,BuildingType,HouseUse,HouseType,BargainCompanyName,VenderDepartId,VenderDepartName,");
            sb.Append("VenderUserId,VenderUserName,VenderId,VenderName,VenderNo,VenderPhone,VenderMobile,b.FixDiscountFinal,");
            sb.Append("BuyerDepartId,BuyerDepartName,BuyerUserId,BuyerUserName,BuyerId,BuyerName,BuyerNo,BuyerPhone,BuyerMobile,");
            sb.Append("VStanCommision,BStanCommision,AppEmployee,AppDepart,ConfirmEmplyee,ConfirmDepart,a.AddTime as AddTime,");
            sb.Append("VRealCommision,BRealCommision,VCommision,BCommision,VConsulting,BConsulting,a.VSTDiscount,a.BSTDiscount,STMemo,");
            sb.Append("a.UpdateTime,bArchivement,LockStatus,LastCreateArchDate,CreateArchEmp,PointRemark,ArchivementDate,CustomerSource,");
            sb.Append("dbo.f_get_contract_pointallot('Depart',a.ContractId,'10') AS V_Departs," +
                "dbo.f_get_contract_pointallot('Employee',a.ContractId,'10') as V_Users," +
                "dbo.f_get_contract_pointallot('Point',a.ContractId,'10') as V_Points," +
                "dbo.f_get_contract_pointallot('Depart',a.ContractId,'20') AS B_Departs," +
                "dbo.f_get_contract_pointallot('Employee',a.ContractId,'20') as B_Users," +
                "dbo.f_get_contract_pointallot('Point',a.ContractId,'20') as B_Points," +
                "dbo.f_get_contract_pointallot('Depart',a.ContractId,'All') AS Departs," +
                "dbo.f_get_contract_pointallot('Employee',a.ContractId,'All') as Users," +
                "dbo.f_get_contract_pointallot('Point',a.ContractId,'All') as Points,");
            sb.Append("VRealCommisionFinal,BRealCommisionFinal,VCommisionFinal,BCommisionFinal,VConsultingFinal,BConsultingFinal,");
            sb.Append("VCommisionGeted,BCommisionGeted,VConsultingPayed,BConsultingPayed,BCommisionGetedPayed,VCommisionGetedPayed,");
            sb.Append("a.ContractCityId,a.ContractCityName,a.ContractCompanyId,a.ContractCompanyName,a.ContractDepartmentName,Memo,DiscountRate,");
            sb.Append("case when VRealCommisionFinal>VCommisionGeted then convert(decimal(18,2),VRealCommisionFinal-VCommisionGeted) else 0 end as VNotGeted,");
            sb.Append("case when BRealCommisionFinal>BCommisionGeted then convert(decimal(18,2),BRealCommisionFinal-BCommisionGeted) else 0 end as BNotGeted");

            
            //所要查询的表名
            string TableName = "t_Contract_BaseInfo a inner join t_Contract_CommisionInfo b on a.contractId = b.ContractId left Join t_Contract_LackItemsInfo c on a.ContractId = c.ContractId ";
            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strPageIndex,strPageSize, strOrderField, TableName, strWhere, 
                FieldList.ToString(), sb.ToString(), true);
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet getContractList(string strWhere, int strPageIndex, int strPageSize,string OrderField)
        {
            //排序
            string strOrderField = OrderField;
            //欲选择字段列表 
            StringBuilder FieldList = new StringBuilder();
            FieldList.Append("Finished,ContractUserName,ContractId,ContractDate,ContractCode,BargainType,BargainPrice,");
            FieldList.Append("HouseAddress,Region,VenderName,BuyerName,LoanInfo,Bank,AccptInfo,");
            FieldList.Append("LoanGiveInfo,asVDepart,asVEmployee,asVPoint,asBDepart,asBEmployee,asBPoint,");
            FieldList.Append("NotarizatInfo,MortagageInfo,TransferInfo,Memo");
            //FieldList.Append("Memo");

            ////欲选择字段列表 
            //string FieldList = "ContractUserName,VCommisionGeted,BCommisionGeted,VConsultingPayed,BConsultingPayed,BCommisionGetedPayed,VCommisionGetedPayed,asVDepart,asVPoint,asVEmployee,asBDepart,asBPoint,asBEmployee,ContractId,ApplyId,ContractCode,ContractDate,BusinessDescribe,BusinessType,BargainType,BargainPrice,HouseId,HouseCode,HouseAddress,Area,CommunityId,Community,CommunityAddress,PropertyCode,Region,Block,CurrentFloor,TotalFloor,BuildingType,HouseUse,HouseType,BargainCityId,BargainCityName,BargainCompanyId,BargainCompanyName,BargainDepartmentId,BargainDepartmentName,BargainUserId,BargainUserName,VenderCityId,VenderCityName,VenderCompanyId,VenderCompanyName,VenderDepartId,VenderDepartName,VenderUserId,VenderUserName,VenderId,VenderName,VenderNo,VenderPhone,VenderMobile,BuyerCityId,BuyerCityName,BuyerCompanyId,BuyerCompanyName,BuyerDepartId,BuyerDepartName,BuyerUserId,BuyerUserName,BuyerId,BuyerName,BuyerNo,BuyerPhone,BuyerMobile,VStanCommision,BStanCommision,VRealCommision,BRealCommision,VCommision,BCommision,VConsulting,BConsulting,AppEmployeeId,AppEmployee,AppDepartId,AppDepart,AppCompanyId,AppCompany,AppCityId,AppCity,ConfirmEmplyeeId,ConfirmEmplyee,ConfirmDepartId,ConfirmDepart,ConfirmCompanyId,ConfirmCompany,ConfirmCityId,ConfirmCity,AddTime,UpdateCityId,UpdateCityName,UpdateCompanyId,UpdateCompanyName,UpdateDepartmentId,UpdateDepartmentName,UpdateUserId,UpdateUserName,UpdateTime,bArchivement,LockStatus,LastCreateArchDate,CreateArchEmpId,CreateArchEmp,PointRemark";
            ////子查询欲选择字段列表
            //StringBuilder sb = new StringBuilder();
            //sb.Append("ContractUserName,ContractId,ApplyId,ContractCode,ContractDate,BusinessDescribe,BusinessType,BargainType,BargainPrice,HouseId,HouseCode,HouseAddress,Area,CommunityId,Community,CommunityAddress,PropertyCode,Region,Block,CurrentFloor,TotalFloor,BuildingType,HouseUse,HouseType,BargainCityId,BargainCityName,BargainCompanyId,BargainCompanyName,BargainDepartmentId,BargainDepartmentName,BargainUserId,BargainUserName,VenderCityId,VenderCityName,VenderCompanyId,VenderCompanyName,VenderDepartId,VenderDepartName,VenderUserId,VenderUserName,VenderId,VenderName,VenderNo,VenderPhone,VenderMobile,BuyerCityId,BuyerCityName,BuyerCompanyId,BuyerCompanyName,BuyerDepartId,BuyerDepartName,BuyerUserId,BuyerUserName,BuyerId,BuyerName,BuyerNo,BuyerPhone,BuyerMobile,VStanCommision,BStanCommision,AppEmployeeId,AppEmployee,AppDepartId,AppDepart,AppCompanyId,AppCompany,AppCityId,AppCity,ConfirmEmplyeeId,ConfirmEmplyee,ConfirmDepartId,ConfirmDepart,ConfirmCompanyId,ConfirmCompany,ConfirmCityId,ConfirmCity,AddTime,UpdateCityId,UpdateCityName,UpdateCompanyId,UpdateCompanyName,UpdateDepartmentId,UpdateDepartmentName,UpdateUserId,UpdateUserName,UpdateTime,bArchivement,LockStatus,LastCreateArchDate,CreateArchEmpId,CreateArchEmp,PointRemark");
            //sb.Append(",(stuff((select '/'+t_Contract_PointAllotInfo.Depart from t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId   and t_Contract_PointAllotInfo.BuyerOrVenderInfo =10 for xml path('') ), 1, 1, '')) AS asVDepart ");
            //sb.Append(",(stuff((select '/'+Convert(varchar(max),t_Contract_PointAllotInfo.Point) from t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId and t_Contract_PointAllotInfo.BuyerOrVenderInfo =10 for xml path('') ), 1, 1, '')) AS asVPoint ");
            //sb.Append(",(stuff((select '/'+t_Contract_PointAllotInfo.Employee from t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId and t_Contract_PointAllotInfo.BuyerOrVenderInfo =10 for xml path('') ), 1, 1, '')) AS asVEmployee ");
            //sb.Append(",(stuff((select '/'+t_Contract_PointAllotInfo.Depart from t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId   and t_Contract_PointAllotInfo.BuyerOrVenderInfo =20  for xml path('') ), 1, 1, '')) AS asBDepart ");
            //sb.Append(",(stuff((select '/'+Convert(varchar(max),t_Contract_PointAllotInfo.Point) from t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId    and t_Contract_PointAllotInfo.BuyerOrVenderInfo =20  for xml path('') ), 1, 1, '')) AS asBPoint ");
            //sb.Append(",(stuff((select '/'+t_Contract_PointAllotInfo.Employee from t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId and t_Contract_PointAllotInfo.BuyerOrVenderInfo =20  for xml path('') ), 1, 1, '')) AS asBEmployee ");

            //sb.Append(",(select t_Contract_CommisionInfo.VCommisionGeted  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as VCommisionGeted");
            //sb.Append(",(select t_Contract_CommisionInfo.BCommisionGeted  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as BCommisionGeted");
            //sb.Append(",(select t_Contract_CommisionInfo.VConsultingPayed  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as VConsultingPayed");
            //sb.Append(",(select t_Contract_CommisionInfo.BConsultingPayed  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as BConsultingPayed");
            //sb.Append(",(select t_Contract_CommisionInfo.BCommisionGetedPayed  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as BCommisionGetedPayed");
            //sb.Append(",(select t_Contract_CommisionInfo.VCommisionGetedPayed  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as VCommisionGetedPayed");

            //sb.Append(",(select t_Contract_CommisionInfo.VRealCommisionFinal  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as VRealCommision");
            //sb.Append(",(select t_Contract_CommisionInfo.BRealCommisionFinal  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as BRealCommision");
            //sb.Append(",(select t_Contract_CommisionInfo.VCommisionFinal  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as VCommision");
            //sb.Append(",(select t_Contract_CommisionInfo.BCommisionFinal  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as BCommision");
            //sb.Append(",(select t_Contract_CommisionInfo.VConsultingFinal  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as VConsulting");
            //sb.Append(",(select t_Contract_CommisionInfo.BConsultingFinal  from t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId =t_Contract_BaseInfo.ContractId) as BConsulting");

            //子查询欲选择字段列表
            StringBuilder sb = new StringBuilder();
            sb.Append("c.Finished as Finished,a.ContractUserName as ContractUserName,a.ContractId as ContractId,a.ContractDate as ContractDate,a.ContractCode as ContractCode,a.BargainType as BargainType,");
            sb.Append("a.BargainPrice as BargainPrice,a.HouseAddress as HouseAddress,a.Region as Region,a.VenderName as VenderName,");
            sb.Append("a.BuyerName as BuyerName,b.LoanInfo as LoanInfo,c.Bank as Bank,c.AccptInfo as AccptInfo,d.CreateLoanGiveInfo as LoanGiveInfo,");
            sb.Append("dbo.f_get_contract_pointallot('Depart',a.ContractId,'10') as asVDepart,");
            sb.Append("dbo.f_get_contract_pointallot('Employee',a.ContractId,'10') as asVEmployee,");
            sb.Append("dbo.f_get_contract_pointallot('Point',a.ContractId,'10') as asVPoint,");
            sb.Append("dbo.f_get_contract_pointallot('Depart',a.ContractId,'20') as asBDepart,");
            sb.Append("dbo.f_get_contract_pointallot('Employee',a.ContractId,'20') as asBEmployee,");
            sb.Append("dbo.f_get_contract_pointallot('Point',a.ContractId,'20') as asBPoint,");
            sb.Append("dbo.udf_GetCreateNotarizatInfo(a.ContractId) as NotarizatInfo,");
            sb.Append("dbo.udf_GetCreateMortagageInfo(a.ContractId) as MortagageInfo,");
            sb.Append("dbo.udf_CreateRecieveInfo(a.ContractId) as TransferInfo,");
            sb.AppendFormat("a.Memo as Memo");

            //所要查询的表名
            string TableName = "t_Contract_BaseInfo a left join t_Contract_Infomation b on a.ContractId=b.ContractId left join t_Contract_Infomation1 c on a.ContractId=c.ContractId left join t_Preliminary_SubInfo3 d on a.ContractId=d.ContractId ";
            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strPageIndex, strPageSize, strOrderField, TableName, strWhere, FieldList.ToString(), sb.ToString(), true);
        }
        
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet getTransferList(string strWhere, int strPageIndex, int strPageSize)
        {
            //排序
            string strOrderField = " ReciveDate desc";
            //欲选择字段列表 
            string FieldList = "ContractId,HouseId,ContractCode,Region,HouseAddress,VenderUserName,BuyerUserName,VenderDepartName,BuyerDepartName,PayStyle,DealDate,ReciveDate,SendDate,ReciveCode,AccptDate,AccptInfo,CertificationPserson,Finished,LackInfo,VPoint,BPoint,LoanDealMode,ContractDate";
            //子查询欲选择字段列表
            StringBuilder sb = new StringBuilder();
            sb.Append("a.ContractId,a.HouseId,a.ContractCode,a.Region,a.HouseAddress,a.VenderUserName,a.BuyerUserName,a.VenderDepartName,a.BuyerDepartName,(case b.LoanInfo when '一次性付款' then '一次性' when '普通贷款' then '贷款' when '转按揭贷款' then '贷款' end) as PayStyle,b.DealDate,c.ReciveDate,c.SendDate,c.ReciveCode,c.AccptDate,c.AccptInfo,c.CertificationPserson,c.Finished,d.LackDocument as LackInfo,");
            sb.Append("dbo.f_get_contract_pointallot('Point',a.ContractId,'10') as VPoint,");
            sb.Append("dbo.f_get_contract_pointallot('Point',a.ContractId,'20') as BPoint,");
            //sb.Append("(select LoanDealMode  from t_Preliminary_SubInfo3 where ContractId=a.ContractId) as LoanDealMode,a.ContractDate ");
            sb.Append("e.LoanDealMode as LoanDealMode,a.ContractDate ");
            //所要查询的表名
            string TableName = " t_Contract_BaseInfo a left join t_Contract_Infomation b on a.ContractId = b.ContractId left join t_Contract_Infomation1 c on a.ContractId = c.ContractId left join t_Contract_LackItemsInfo d on a.ContractId = d.ContractId left join t_Preliminary_SubInfo3 e on a.ContractId = e.ContractId ";


            //string FieldList = "ContractId,HouseId,ContractCode,Region,HouseAddress,VenderUserName,BuyerUserName,VenderDepartName,BuyerDepartName,PayStyle,LoanDealMode,DealDate,SendDate,ReciveCode,AccptInfo,CertificationPserson,Finished,LackInfo";
            ////子查询欲选择字段列表
            //StringBuilder sb = new StringBuilder();
            //sb.Append("a.ContractId,a.HouseId,a.ContractCode,a.Region,a.HouseAddress,a.VenderUserName,a.BuyerUserName,a.VenderDepartName,a.BuyerDepartName,b.PayStyle,e.LoanDealMode,b.DealDate,c.SendDate,c.ReciveCode,c.AccptInfo,c.CertificationPserson,c.Finished,d.LackDocument as LackInfo");

            ////所要查询的表名
            //string TableName = " t_Contract_BaseInfo a left join t_Contract_Infomation b on a.ContractId = b.ContractId left join t_Contract_Infomation1 c on a.ContractId = c.ContractId left join t_Contract_LackItemsInfo d on a.ContractId = d.ContractId left join t_Preliminary_SubInfo3 e on a.ContractId = e.ContractId ";
            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strPageIndex, strPageSize, strOrderField, TableName, strWhere, FieldList, sb.ToString(), true);
        }
    }
}
